﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using QuanLyBanHang.DataConnection;
using System.Windows.Forms;
using System.Data;

namespace QuanLyBanHang.QuerySQL
{
    class ClassTKDT
    {
        Connect conn = new Connect();
        // Thống kê doanh thu
        public DataTable ShowHD(int i, int y = 0, int m = 0, int d = 0)
        {
            DataTable tb = new DataTable(); ;
            switch(i){
                case 0:
                    {
                        try
                        {
                            string sql = "select MAHD,HOTEN,TENNV,NGAYLAP,TONGTIEN from HOADON,KHACHHANG,NHANVIEN where HOADON.MAKH = KHACHHANG.MAKH and HOADON.MANV = NHANVIEN.MANV";
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 1:
                    {
                        try
                        {
                            string sql = "select MAHD,HOTEN,TENNV,NGAYLAP,TONGTIEN from HOADON,KHACHHANG,NHANVIEN where HOADON.MAKH = KHACHHANG.MAKH and HOADON.MANV = NHANVIEN.MANV and year(NGAYLAP) = " + y + " and month(NGAYLAP) = " + m + " and day(NGAYLAP) = " + d;
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 2:
                    {
                        try
                        {
                            string sql = "select MAHD,HOTEN,TENNV,NGAYLAP,TONGTIEN from HOADON,KHACHHANG,NHANVIEN where HOADON.MAKH = KHACHHANG.MAKH and HOADON.MANV = NHANVIEN.MANV and year(NGAYLAP) = " + y + " and month(NGAYLAP) = " + m;
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 3:
                    {
                        try
                        {
                            string sql = "select MAHD,HOTEN,TENNV,NGAYLAP,TONGTIEN from HOADON,KHACHHANG,NHANVIEN where HOADON.MAKH = KHACHHANG.MAKH and HOADON.MANV = NHANVIEN.MANV and year(NGAYLAP) = " + y;
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                }
            return tb;
        }
        public DataTable thongke(int i, int y = 0, int m = 0, int d = 0)
        {
            DataTable tb = new DataTable();
            switch(i){
                case 0:
                    {
                        try
                        {
                            string sql = "select sum(TONGTIEN) as TONG from HOADON where year(NGAYLAP) = " + y + " and month(NGAYLAP) = " + m + " and day(NGAYLAP) = " + d;
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 1:
                    {
                        try
                        {
                            string sql = "select sum(TONGTIEN) as TONG from HOADON where year(NGAYLAP) = " + y + " and month(NGAYLAP) = " + m;
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 2:
                    {
                        try
                        {
                            string sql = "select sum(TONGTIEN) as TONG from HOADON where year(NGAYLAP) = "+ y;
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
            }
            return tb;
        }
        //Thống kê số lượng nhập
        public DataTable ShowHDN(int i, int y = 0, int m = 0, int d = 0)
        {
            DataTable tb = new DataTable();
            switch(i){
                case 0:
                    {
                        try
                        {
                            string sql = "select MAPN,TENNCC,TENNV,NGAYLAP,TONGTIEN from PHIEUNHAP,NHACC,NHANVIEN where PHIEUNHAP.MANCC=NHACC.MANCC and PHIEUNHAP.MANV = NHANVIEN.MANV";
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 1:
                    {
                        try
                        {
                            string sql = "select MAPN,TENNCC,TENNV,NGAYLAP,TONGTIEN from PHIEUNHAP,NHACC,NHANVIEN where PHIEUNHAP.MANCC=NHACC.MANCC and PHIEUNHAP.MANV = NHANVIEN.MANV and year(NGAYLAP) = " + y + " and month(NGAYLAP) = " + m + " and day(NGAYLAP) = " + d;
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 2:
                    {
                        try
                        {
                            string sql = "select MAPN,TENNCC,TENNV,NGAYLAP,TONGTIEN from PHIEUNHAP,NHACC,NHANVIEN where PHIEUNHAP.MANCC=NHACC.MANCC and PHIEUNHAP.MANV = NHANVIEN.MANV and year(NGAYLAP) = " + y + " and month(NGAYLAP) = " + m;
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 3:
                    {
                        try
                        {
                            string sql = "select MAPN,TENNCC,TENNV,NGAYLAP,TONGTIEN from PHIEUNHAP,NHACC,NHANVIEN where PHIEUNHAP.MANCC=NHACC.MANCC and PHIEUNHAP.MANV = NHANVIEN.MANV and year(NGAYLAP) = " + y;
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
        }
            return tb;
        }
        public DataTable thongkenhap(int i, int y = 0, int m = 0, int d = 0)
        {
            DataTable tb = new DataTable();
            switch (i)
            {
                case 0:
                    {
                        try
                        {
                            string sql = "select sum(TONGTIEN) as TONG from PHIEUNHAP where year(NGAYLAP) = " + y + " and month(NGAYLAP) = " + m + " and day(NGAYLAP) = " + d;
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 1:
                    {
                        try
                        {
                            string sql = "select sum(TONGTIEN) as TONG from PHIEUNHAP where year(NGAYLAP) = " + y + " and month(NGAYLAP) = " + m;
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 2:
                    {
                        try
                        {
                            string sql = "select sum(TONGTIEN) as TONG from PHIEUNHAP where year(NGAYLAP) = " + y;
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
            }
            return tb;
        }
    }
}
